package service;

/**
 * 业务服务对象的一些标准 关注于业务逻辑的实现,实现清晰的业务接口定义 逻辑清晰,代码简约 业务操作应该具有日志纪录 捕获异常必须写入日志
 * 尽量采用EJB标准或Hibernate的SQL 如果可能,尽量采用命名查询 SQL语句尽量避免非标准以及过于复杂的SQL,避免依赖数据库特定的函数
 * 将一部分SQL完成的功能放到Java里实现来消除对特定数据库的依赖(如格式转换问题,日期样式等) 查询语句尽量采用参数化的方式避免直接写入SQL语句
 * 适当注意SQL的执行时间长短. 用好AppException 必须写单元测试
 */
import java.beans.PropertyDescriptor;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Locale;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.log4j.Logger;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.type.Type;

/**
 * Hsql 调试 Service
 * @author sunj
 * 
 */
public class DbMainService {

    /** Hibernate SessionFactory. */
    private SessionFactory sessionFactory;

    /**
     * Logger for this class.
     */
    private static final Logger logger = Logger.getLogger(DbMainService.class);

    /**
     * 执行hql
     * @param hql 查询语句
     * @param result
     * @return String
     */
    @SuppressWarnings("unchecked")
    public String getResultByHql(String hql, Collection<Object[]> result) {
        Session session = sessionFactory.getCurrentSession();
        Query qry = null;
        try {
            qry = session.createQuery(hql);
        } catch (Exception e) {
            String msg = "（错误信息='Hql 语法有误" + e.getMessage() + "')";
            return "!!!" + msg;
        }
        Type[] types = qry.getReturnTypes();
        ArrayList<Object[]> columns = new ArrayList<Object[]>();
        String retunInfo = getColumnsOfHql(types, columns);
        // 第一行是 column,以后是data
        Object[] columnsAry = columns.get(0);
        result.add(columnsAry);
        if (types.length == 1) {
            Collection<Object> record = qry.list();
            for (Object value : record) {
                result.add(getColumnValueOfHql(value).toArray());
            }

        } else {
            Collection<Object[]> recordAry = qry.list();
            for (Object[] values : recordAry) {
                ArrayList<Object> temp = new ArrayList<Object>();
                for (Object value : values) {
                    temp.addAll(getColumnValueOfHql(value));
                }
                result.add(temp.toArray());
            }
        }
        return retunInfo;
    }

    /**
     * 取列名
     * @param types types
     * @param columns columns
     * @return String String
     */
    @SuppressWarnings("unchecked")
    private String getColumnsOfHql(Type[] types, Collection<Object[]> columns) {
        StringBuilder columnInfos = new StringBuilder();
        ArrayList<String> columnNamesAry = new ArrayList<String>();
        if (types.length == 1) {
            columnInfos.append("return ");
        } else {
            columnInfos.append("return Object[] of ");
        }
        for (Type type : types) {
            columnInfos.append(" ").append(type.getName());
            Class clmnCls = type.getReturnedClass();
            if (isBaseType(clmnCls)) {
                columnNamesAry.add(clmnCls.getCanonicalName());
            } else {// java bean
                for (PropertyDescriptor propDesc : PropertyUtils
                        .getPropertyDescriptors(clmnCls)) {
                    String propName = propDesc.getName();
                    if (!"class".equals(propName)) {
                        columnNamesAry.add(propName);
                    }
                }
            }

        }
        String[] columnsAry = new String[columnNamesAry.size()];
        columnNamesAry.toArray(columnsAry);
        columns.add(columnsAry);
        return columnInfos.toString();
    }

    /**
     * 取值
     * @param value value
     * @return ArrayList<Object> ArrayList<Object>
     */
    @SuppressWarnings("unchecked")
    private Collection<Object> getColumnValueOfHql(Object value) {
        ArrayList<Object> temp = new ArrayList<Object>();
        Class valueClass = value.getClass();
        if (isBaseType(valueClass)) {
            temp.add(value);
        } else {
            for (PropertyDescriptor propDesc : PropertyUtils
                    .getPropertyDescriptors(valueClass)) {
                String propName = propDesc.getName();
                if (!"class".equals(propName)) {
                    try {
                        temp.add(PropertyUtils.getProperty(value, propName));
                    } catch (Exception e) {
                        logger.error(e.getMessage());
                    }
                }
            }
        }
        return temp;
    }

    /**
     * 执行标准sql
     * @param sql 查询语句
     * @param result
     * @return String
     */
    @SuppressWarnings({ "unchecked", "deprecation" })
    public String getResultBySql(String sql, Collection<Object[]> result) {
        Session session = sessionFactory.getCurrentSession();
        String retunInfo = null;
        Connection con = null;
        Statement stmt = null;
        ResultSet resultSet = null;
        try {
            con = session.connection();
            stmt = con.createStatement();
            if (sql.toUpperCase(Locale.getDefault()).startsWith("SELECT")) {
                resultSet = stmt.executeQuery(sql);
                ResultSetMetaData reMeData = resultSet.getMetaData();
                tryGetResult(resultSet, reMeData, result);
            } else {
                stmt.execute(sql);
            }

        } catch (SQLException e) {
            String msg = "（错误信息='" + e.getMessage() + "' SQL状态值='"
                    + e.getSQLState() + "' 错误代码='" + e.getErrorCode() + "')";
            return "!!!" + msg;

        } finally {
            if (null != resultSet) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    logger.error(e.getMessage());
                }
            }
            if (null != stmt) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    logger.error(e.getMessage());
                }
            }
            if (null != con) {
                try {
                    con.close();
                } catch (SQLException e) {
                    logger.error(e.getMessage());
                }
            }
        }
        retunInfo = "SQL";
        return retunInfo;
    }

    /**
     * tryGetResult
     * @param resultSet resultSet
     * @param reMeData reMeData
     * @param result result
     */
    private void tryGetResult(ResultSet resultSet, ResultSetMetaData reMeData,
            Collection<Object[]> result) {
        int colcount = 0;
        try {
            colcount = reMeData.getColumnCount();
            String rowColumn[] = new String[colcount];
            for (int i = 0; i < colcount; i++) {
                rowColumn[i] = reMeData.getColumnName(i + 1);
            }
            result.add(rowColumn);
            while (resultSet.next()) {
                Object[] rowValue = new Object[colcount];
                for (int i = 0; i < colcount; i++) {
                    rowValue[i] = resultSet.getObject(i + 1);
                }
                result.add(rowValue);
            }
        } catch (Exception e) {
            logger.error(e.getMessage());
        }
    }

    /**
     * 查询数据库所有表的信息
     * @return ArrayList
     * @throws HibernateException
     */
    @SuppressWarnings({ "unchecked", "deprecation"})
    public Collection getDbTable() {
        Session session = sessionFactory.getCurrentSession();
        DatabaseMetaData dBMetaData = null;
        ResultSet tableRet = null;
        ArrayList<String> tableList = new ArrayList<String>();
        try {
            dBMetaData = session.connection().getMetaData();
            tableRet = dBMetaData.getTables(null, "%", "%",
                    new String[] { "TABLE" });
            while (tableRet.next()) {
                tableList.add(tableRet.getString("TABLE_NAME"));
            }
        } catch (Exception e) {
            logger.error("get tables err", e);
        } finally {
            try {
                if (tableRet != null) {
                    tableRet.close();
                }
            } catch (SQLException e) {
                logger.error("close err " + e.getMessage());
            }
        }
        Collections.sort(tableList);
        return tableList;
    }

    /**
     * 查询数据库表结构的详细信息
     * @param table
     * @return ArrayList
     */
    @SuppressWarnings({ "unchecked", "deprecation" })
    public Collection<Object[]> getDbTableDetail(String table) {
        Session session = sessionFactory.getCurrentSession();
        DatabaseMetaData DBMetaData = null;
        ResultSet colRet = null;
        String columnName = null;
        String columnType = null;
        int datasize;
        int digits;
        int nullable;
        ArrayList<Object[]> tableDetail = new ArrayList<Object[]>();
        try {
            DBMetaData = session.connection().getMetaData();
            colRet = DBMetaData.getColumns(null, "%", table, "%");
            while (colRet.next()) {
                Object[] column = new Object[5];
                int i = 0;
                columnName = colRet.getString("COLUMN_NAME");
                columnType = colRet.getString("TYPE_NAME");
                datasize = colRet.getInt("COLUMN_SIZE");
                digits = colRet.getInt("DECIMAL_DIGITS");
                nullable = colRet.getInt("NULLABLE");
                column[i++] = columnName;
                column[i++] = columnType;
                column[i++] = datasize;
                column[i++] = digits;
                column[i++] = nullable;
                tableDetail.add(column);
            }
        } catch (Exception e) {
            logger.error(e.getMessage());
        } finally {
            try {
                colRet.close();
            } catch (SQLException e) {
                logger.error(e.getMessage());
            }
        }

        return tableDetail;
    }

    /**
     * 生成select * sql
     * @param table
     * @param colNum
     * @param colValue
     * @return ArrayList
     */
    @SuppressWarnings({ "unchecked", "deprecation" })
    public String getSelectOfSql(String table, Integer colNum, String[] colValue) {
        Session session = sessionFactory.getCurrentSession();
        StringBuffer sql = new StringBuffer();
        StringBuffer sqlWhere = new StringBuffer();
        sqlWhere.append(" where");
        sql.append("select * from ");
        sql.append(table);
        DatabaseMetaData DBMetaData = null;
        ResultSet colRet = null;
        String columnName = null;
        String columnType = null;
        try {
            DBMetaData = session.connection().getMetaData();
            colRet = DBMetaData.getColumns(null, "%", table, "%");
            Integer i = 0;
            boolean start = true;
            while (colRet.next()) {
                columnName = colRet.getString("COLUMN_NAME");
                columnType = colRet.getString("TYPE_NAME");
                if (!"".equals(colValue[i]) && null != colValue[i]) {
                    if (start) {
                        appendSelectOfSql(start, columnName, columnType,
                                sqlWhere, colValue[i]);
                        start = false;

                    } else {
                        appendSelectOfSql(start, columnName, columnType,
                                sqlWhere, colValue[i]);
                    }

                }
                i++;
            }
        } catch (Exception e) {
            logger.error(e.getMessage());
        } finally {
            try {
                colRet.close();
            } catch (SQLException e) {
                logger.error(e.getMessage());
            }
        }
        if (sqlWhere.length() > 6) {
            sql.append(sqlWhere);
        }
        return sql.toString();
    }

    /**
     * 拼接组成sql语句 where条件
     * @param start start
     * @param columnName columnName
     * @param columnType columnType
     * @param sqlWhere sqlWhere
     * @param colValue colValue
     */
    @SuppressWarnings("unchecked")
    public void appendSelectOfSql(boolean start, String columnName,
            String columnType, StringBuffer sqlWhere, String colValue) {
        if (start) {
            if ("INTEGER".equals(columnType) || "int".equals(columnType)
                    || "Long".equals(columnType)) {
                sqlWhere.append(" " + columnName + "=" + colValue);
            } else {
                sqlWhere.append(" " + columnName + "='" + colValue + "'");
            }
        } else {
            if ("INTEGER".equals(columnType) || "int".equals(columnType)
                    || "Long".equals(columnType)) {
                sqlWhere.append(" and " + columnName + "=" + colValue);
            } else {
                sqlWhere.append(" and " + columnName + "='" + colValue + "'");
            }
        }
    }

    /**
     * 生成select count(*) sql
     * @param table
     * @param colNum
     * @param colValue
     * @return ArrayList
     */
    @SuppressWarnings({ "unchecked", "deprecation" })
    public String getSelectCountOfSql(String table, Integer colNum,
            String[] colValue) {
        Session session = sessionFactory.getCurrentSession();
        StringBuffer sql = new StringBuffer();
        StringBuffer sqlWhere = new StringBuffer();
        sqlWhere.append(" where");
        sql.append("select count(*) from ");
        sql.append(table);
        DatabaseMetaData DBMetaData = null;
        ResultSet colRet = null;
        String columnName = null;
        String columnType = null;
        try {
            DBMetaData = session.connection().getMetaData();
            colRet = DBMetaData.getColumns(null, "%", table, "%");
            Integer i = 0;
            boolean start = true;
            while (colRet.next()) {
                columnName = colRet.getString("COLUMN_NAME");
                columnType = colRet.getString("TYPE_NAME");
                if (!"".equals(colValue[i]) && null != colValue[i]) {
                    if (start) {
                        appendSelectOfSql(start, columnName, columnType,
                                sqlWhere, colValue[i]);
                        start = false;
                    } else {
                        appendSelectOfSql(start, columnName, columnType,
                                sqlWhere, colValue[i]);
                    }
                }
                i++;
            }
        } catch (Exception e) {
            logger.error(e.getMessage());
        } finally {
            try {
                colRet.close();
            } catch (SQLException e) {
                logger.error(e.getMessage());
            }
        }
        if (sqlWhere.length() > 6) {
            sql.append(sqlWhere);
        }
        return sql.toString();
    }

    /**
     * 生成create sql
     * @param table
     * @param colNum
     * @param colValue
     * @return ArrayList
     */
    @SuppressWarnings({ "unchecked", "deprecation" })
    public String getCreateOfSql(String table, Integer colNum, String[] colValue) {
        Session session = sessionFactory.getCurrentSession();
        StringBuffer sql = new StringBuffer();
        StringBuffer sqlColumn = new StringBuffer();
        StringBuffer sqlValue = new StringBuffer();
        sql.append("INSERT INTO ");
        sql.append(table);
        sqlColumn.append(" (");
        sqlValue.append(" (");
        DatabaseMetaData DBMetaData = null;
        ResultSet colRet = null;
        String columnName = null;
        String columnType = null;
        try {
            DBMetaData = session.connection().getMetaData();
            colRet = DBMetaData.getColumns(null, "%", table, "%");
            Integer i = 0;
            boolean start = true;
            while (colRet.next()) {
                columnName = colRet.getString("COLUMN_NAME");
                columnType = colRet.getString("TYPE_NAME");
                if (!"".equals(colValue[i]) && null != colValue[i]) {
                    if (start) {
                        appendInsertOfSql(start, columnName, columnType,
                                sqlColumn, sqlValue, colValue[i]);
                        start = false;
                    } else {
                        appendInsertOfSql(start, columnName, columnType,
                                sqlColumn, sqlValue, colValue[i]);
                    }
                }
                i++;
            }
        } catch (Exception e) {
            logger.error(e.getMessage());
        } finally {
            try {
                colRet.close();
            } catch (SQLException e) {
                logger.error(e.getMessage());
            }
        }
        sqlColumn.append(" ) ");
        sqlValue.append(" ) ");
        if (sqlColumn.length() > 2) {
            sql.append(sqlColumn).append("VALUES ").append(sqlValue);
        }
        return sql.toString();
    }

    /**
     * 拼接组成SQL语句中的insert 语句
     * @param start start
     * @param columnName columnName
     * @param columnType columnType
     * @param sqlColumn sqlColumn
     * @param sqlValue sqlValue
     * @param colValue colValue
     */
    @SuppressWarnings("unchecked")
    public void appendInsertOfSql(boolean start, String columnName,
            String columnType, StringBuffer sqlColumn, StringBuffer sqlValue,
            String colValue) {
        if (start) {
            if ("INTEGER".equals(columnType) || "int".equals(columnType)
                    || "Long".equals(columnType)) {
                sqlColumn.append(columnName);
                sqlValue.append(colValue);
            } else {
                sqlColumn.append(columnName);
                sqlValue.append("'" + colValue + "'");
            }
        } else {
            if ("INTEGER".equals(columnType) || "int".equals(columnType)
                    || "Long".equals(columnType)) {
                sqlColumn.append(", ").append(columnName);
                sqlValue.append(", ").append(colValue);
            } else {
                sqlColumn.append(", ").append(columnName);
                sqlValue.append(" ,'" + colValue + "'");
            }
        }

    }

    /**
     * 
     * 判断某个类是否为JAVA的基本类型
     * 
     * @param type
     * 
     * @return boolean
     * 
     */

    @SuppressWarnings("unchecked")
    private boolean isBaseType(Class type) {

        return (

        String.class.equals(type)

        || int.class.equals(type)

        || Integer.class.equals(type)

        || long.class.equals(type)

        || Long.class.equals(type)

        || double.class.equals(type)

        || Double.class.equals(type)

        || float.class.equals(type)

        || Float.class.equals(type)

        || byte.class.equals(type)

        || Byte.class.equals(type)

        || boolean.class.equals(type)

        || Boolean.class.equals(type)

        || short.class.equals(type)

        || Short.class.equals(type)

        || char.class.equals(type)

        || Time.class.equals(type)

        || Timestamp.class.equals(type)

        || java.util.Date.class.equals(type)

        || Date.class.equals(type));

    }

    /**
     * @param pSessionFactory the sessionFactory to set
     */
    public void setSessionFactory(SessionFactory pSessionFactory) {
        sessionFactory = pSessionFactory;
    }

}